﻿using Koala.Pro.CodeGenerator.UI.Common;
using Koala.Pro.CodeGenerator.UI.DatabaseModel.Schema;
using log4net;
using Microsoft.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Text;

namespace Koala.Pro.CodeGenerator.UI.DatabaseModel.Data
{
    public class SqlServer : Database
    {
        private static readonly ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public override IDictionary<string, DatabaseInfo> ListDatabases()
        {
            IDictionary<string, DatabaseInfo> dictionary = new Dictionary<string, DatabaseInfo>();
            string cmdText = $"SELECT name FROM master.dbo.sysdatabases ";
            using SqlConnection sqlConnection = new SqlConnection(base.ConnectString);
            sqlConnection.Open();
            sqlConnection.ChangeDatabase("master");
            SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
            IDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                DatabaseInfo databaseInfo = new DatabaseInfo();
                databaseInfo.Name = new NameElement(dataReader["name"].ToString());
                dictionary.Add(databaseInfo.Name.Name.ToString(), databaseInfo);
            }
            dataReader.Close();
            return dictionary;
        }

        public override IDictionary<string, TableInfo> ListTables(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, TableInfo> dictionary = new Dictionary<string, TableInfo>();
            string cmdText = $"SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
            using SqlConnection sqlConnection = new SqlConnection(base.ConnectString);
            sqlConnection.Open();
            sqlConnection.ChangeDatabase(database);
            SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
            using IDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                string name = dataReader["name"].ToString();
                if (!name.Equals("dtproperties", StringComparison.OrdinalIgnoreCase))
                {
                    var scheme = name.Split('.').First();
                    var tableName = name.Split('.').Last();
                   
                    TableInfo tableInfo = new TableInfo();
                    tableInfo.Scheme = scheme;
                    tableInfo.Name = new NameElement(tableName);
                    if (base.NoSuffixOfTableName != null)
                    {
                        string text2 = StringUtil.RemovePrefix(tableName, base.NoSuffixOfTableName);
                        log.InfoFormat("{0} +++table Alias Name ={1}", database, text2);
                        tableInfo.Name.Alias = new CString(text2);
                    }
                    dictionary.Add(tableInfo.Name.Name.ToString(), tableInfo);
                }
            }
            return dictionary;
        }

        private DataTable GetReaderSchema(string tableName, SqlConnection connection)
        {
            IDbCommand dbCommand = new SqlCommand();
            var name = tableName;
            var parts = tableName.Split('.');
            if (parts.Length == 2)
            {
                name = $"[{parts[0]}].[{parts[1]}]";
            }
            else if (parts.Length == 1)
            {
                name = $"[{parts[0]}]";
            }
            dbCommand.CommandText = $"select * from {name}";
            dbCommand.Connection = connection;
            using IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
            return dataReader.GetSchemaTable();
        }

        public override IDictionary<string, ColumnInfo> ListColumns(string database, string tableName)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            if (database == "tempdb")
            {
                return new Dictionary<string, ColumnInfo>();
            }

            if (tableName == null)
            {
                throw new ArgumentNullException("tableName");
            }

            IDictionary<string, ColumnInfo> dictionary = new Dictionary<string, ColumnInfo>();
            SqlConnection sqlConnection = new SqlConnection(base.ConnectString);
            sqlConnection.Open();
            sqlConnection.ChangeDatabase(database);
            DataTable readerSchema = GetReaderSchema(tableName, sqlConnection);
            foreach (DataRow row in readerSchema.Rows)
            {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.Name = new NameElement(row["ColumnName"].ToString());
                columnInfo.Ordinal = Convert.ToInt32(row["ColumnOrdinal"].ToString());
                columnInfo.AllowDBNull = (bool)row["AllowDBNull"];
                columnInfo.MaxLength = Convert.ToInt32(row["ColumnSize"].ToString());
                columnInfo.DataTypeId = Convert.ToInt32(row["ProviderType"].ToString());
                columnInfo.DataType = ((SqlDbType)row["ProviderType"]).ToString();
                columnInfo.FieldPrecise = Convert.ToInt32(row["NumericPrecision"].ToString());
                columnInfo.AutoIncrement = (bool)row["IsAutoIncrement"];
                columnInfo.IsPrimaryKey = (bool)row["IsKey"];
                columnInfo.Unique = (bool)row["IsUnique"];
                columnInfo.IsReadOnly = (bool)row["IsReadOnly"];
                string name = row["DataType"].ToString();
                string simplyNetType = GetSimplyNetType(row["DataType"].ToString());
                columnInfo.NetType = new NameElement(name, simplyNetType);
                columnInfo.UIControlType = GetControlType(row["DataType"].ToString());
                dictionary.Add(columnInfo.Name.Name.ToString(), columnInfo);
            }
            SetDefaultValue(dictionary, tableName, sqlConnection);
            SetDescription(dictionary, tableName, sqlConnection);
            SetPrimaryKey(dictionary, tableName, sqlConnection);
            SetForeignKey(dictionary, tableName, sqlConnection);
            sqlConnection.Close();
            return dictionary;
        }

        public override IDictionary<string, ViewInfo> ListViews(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, ViewInfo> dictionary = new Dictionary<string, ViewInfo>();
            using SqlConnection sqlConnection = new SqlConnection(base.ConnectString);
            string text = $"select x.name,y.text from sysobjects x,syscomments y ";
            text += $" where (x.xtype='V') and (x.status>0) and object_id(x.name) = y.id";
            sqlConnection.Open();
            sqlConnection.ChangeDatabase(database);
            SqlCommand sqlCommand = new SqlCommand(text, sqlConnection);
            IDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                ViewInfo viewInfo = new ViewInfo();
                viewInfo.Name = new NameElement(dataReader["name"].ToString());
                viewInfo.Text = dataReader["text"].ToString();
                try
                {
                    dictionary.Add(viewInfo.Name.Name.ToString(), viewInfo);
                }
                catch (ArgumentException)
                {
                    log.Debug("The view name is duplicated:" + viewInfo.Name.Name);
                    log.Debug("The view is ignored");
                }
            }
            dataReader.Close();
            return dictionary;
        }

        public override IDictionary<string, ProcedureInfo> ListProcedures(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, ProcedureInfo> dictionary = new Dictionary<string, ProcedureInfo>();
            using SqlConnection sqlConnection = new SqlConnection(base.ConnectString);
            string text = $"select x.name,y.text from sysobjects x,syscomments y ";
            text += $" where (x.xtype='P') and (x.status>0) and object_id(x.name) = y.id";
            sqlConnection.Open();
            sqlConnection.ChangeDatabase(database);
            SqlCommand sqlCommand = new SqlCommand(text, sqlConnection);
            IDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                ProcedureInfo procedureInfo = new ProcedureInfo();
                procedureInfo.Name = new NameElement(dataReader["name"].ToString());
                procedureInfo.Text = dataReader["text"].ToString();
                try
                {
                    dictionary.Add(procedureInfo.Name.Name.ToString(), procedureInfo);
                }
                catch (ArgumentException)
                {
                    log.Debug("The procedure name is duplicated:" + procedureInfo.Name.Name);
                    log.Debug("The procedure is ignored");
                }
            }
            return dictionary;
        }

        private void SetDefaultValue(IDictionary<string, ColumnInfo> list, string tableName, SqlConnection connection)
        {
            string text = $"SELECT c.name, m.text FROM syscolumns c, syscomments m WHERE c.cdefault = m.id and c.id = object_id('{tableName}')";
            log.Debug("Get DefaultValue sql:" + text);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (ColumnInfo value in list.Values)
            {
                try
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (value.Name.Name.ToString() == row["name"].ToString())
                        {
                            string text2 = row["text"].ToString();
                            if (!string.IsNullOrEmpty(text2))
                            {
                                value.DefaultValue = text2.Substring(1, text2.Length - 2);
                            }
                            log.Debug($"Column {value.Name.Name}'s defaultValue:{value.DefaultValue}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Debug(ex.Message);
                    log.Debug(ex.StackTrace);
                }
            }
        }

        private void SetDescription(IDictionary<string, ColumnInfo> list, string tableName, SqlConnection connection)
        {
             
            string text = $"SELECT * FROM ::fn_listextendedproperty (NULL, 'user','dbo', 'table', '{tableName}', 'column', default)";
            log.Debug("Get Description sql:" + text);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (ColumnInfo value in list.Values)
            {
                try
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (value.Name.Name.ToString() == row["objname"].ToString())
                        {
                            value.Description = row["value"].ToString();
                            log.Debug($"Column {value.Name.Name}'s description:{value.Description}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Debug(ex.Message);
                    log.Debug(ex.StackTrace);
                }
            }
        }

        private void SetPrimaryKey(IDictionary<string, ColumnInfo> list, string tableName, SqlConnection connection)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("select f.*, ");
            stringBuilder.Append("(select name from sysobjects where id=f.rkeyid) as pktable,");
            stringBuilder.Append("(select name from syscolumns where id=f.rkeyid and colid=f.rkey) as pkcolumn ,");
            stringBuilder.Append("(select name from sysobjects where id=f.fkeyid) as fktable,");
            stringBuilder.Append("(select name from syscolumns where id=f.fkeyid and colid=f.fkey) as fkcolumn ");
            stringBuilder.Append("from sysforeignkeys f, syscolumns c ");
            stringBuilder.Append("where c.id=f.rkeyid and f.rkey=c.colid order by pktable");
            string text = stringBuilder.ToString();
            log.Debug("PrimaryKey Sql:" + text);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (KeyValuePair<string, ColumnInfo> item in list)
            {
                if (!item.Value.IsPrimaryKey)
                {
                    continue;
                }
                item.Value.PKBeReferences = new List<KeyInfo>();
                foreach (DataRow row in dataTable.Rows)
                {
                    if (tableName == row["pktable"].ToString() && item.Value.Name.Name.ToString() == row["pkcolumn"].ToString())
                    {
                        string text2 = row["fktable"].ToString();
                        string name = row["fkcolumn"].ToString();
                        KeyInfo keyInfo = new KeyInfo();
                        keyInfo.TableName = new NameElement(text2);
                        keyInfo.ColumnName = new NameElement(name);
                        if (base.NoSuffixOfTableName != null)
                        {
                            string text3 = StringUtil.RemovePrefix(text2, base.NoSuffixOfTableName);
                            log.Debug("+++primaryKey table Alias Name = " + text3);
                            keyInfo.TableName.Alias = new CString(text3);
                        }
                        log.Debug("PrimaryKey be referenced by:" + keyInfo.ToString());
                        item.Value.PKBeReferences.Add(keyInfo);
                    }
                }
            }
        }

        private void SetForeignKey(IDictionary<string, ColumnInfo> list, string tableName, SqlConnection connection)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat("select f.*,'{0}' as fktable, c.name as fkcolumn, ", tableName);
            stringBuilder.Append("(select name from sysobjects where id=f.rkeyid) as pktable,");
            stringBuilder.Append("(select name from syscolumns where id=f.rkeyid and colid=f.rkey) as pkcolumn ");
            stringBuilder.AppendFormat("from sysforeignkeys f, syscolumns c where f.fkeyid=object_id('{0}') ", tableName);
            stringBuilder.Append("and c.id=f.fkeyid and f.fkey=c.colid");
            string text = stringBuilder.ToString();
            log.Debug("ForeignKey Sql:" + text);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (KeyValuePair<string, ColumnInfo> item in list)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    if (item.Value.Name.Name.ToString() == row["fkcolumn"].ToString())
                    {
                        item.Value.IsForeignKey = true;
                        string text2 = row["pktable"].ToString();
                        string name = row["pkcolumn"].ToString();
                        KeyInfo keyInfo = new KeyInfo();
                        keyInfo.TableName = new NameElement(text2);
                        keyInfo.ColumnName = new NameElement(name);
                        if (base.NoSuffixOfTableName != null)
                        {
                            string text3 = StringUtil.RemovePrefix(text2, base.NoSuffixOfTableName);
                            log.Debug("+++foreign key table Alias Name = " + text3);
                            keyInfo.TableName.Alias = new CString(text3);
                        }
                        item.Value.FKReference = keyInfo;
                        log.Debug("ForeignKey:" + keyInfo);
                    }
                }
            }
        }

        public override DataSet ExecuteSql(string database, string query)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            DataSet dataSet = new DataSet();
            using SqlConnection sqlConnection = new SqlConnection(base.ConnectString);
            sqlConnection.Open();
            sqlConnection.ChangeDatabase(database);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
            try
            {
                sqlDataAdapter.Fill(dataSet, Guid.NewGuid().ToString());
                return dataSet;
            }
            finally
            {
                ((IDisposable)(object)sqlDataAdapter)?.Dispose();
            }
        }

        public override bool TestConnection()
        {
            bool result = false;
            IDbConnection dbConnection = new SqlConnection(base.ConnectString);
            try
            {
                if (dbConnection.State == ConnectionState.Closed)
                {
                    dbConnection.Open();
                }
                if (dbConnection.State == ConnectionState.Open)
                {
                    result = true;
                }
            }
            catch
            {
            }
            finally
            {
                dbConnection.Close();
            }
            return result;
        }
    }

}
